﻿using Dapper;
using Microsoft.VisualBasic.ApplicationServices;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using TomatoClock.Models;
using TomatoClock.Views;
using User = TomatoClock.Models.User;

namespace TomatoClock.Services
{
    public class DataService
    {
        string[] dayOfWeekInChinese = { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六" };
        const int ACHIEVEMTS_NUM = 12;
        private readonly string connectionString;
        private readonly string netWorkConnectionString = "";//服务器数据库
        private readonly List<Achievement> achievements;
        private readonly DateTime NowTime=DateTime.Now;

        // 定义事件
        public event Action<Data> OnDataUpdated;
        public event Action AchievementUnlocked;
        public DataService(string connectionString)
        {
            this.connectionString = connectionString;
            var connection = new SQLiteConnection(connectionString);

            // 从数据库加载成就信息
            achievements = LoadAchievementsFromDatabase();

            // 定义成就条件
            foreach (var achievement in achievements)
            {
                switch (achievement.AchievementId)
                {
                    case 1://一天的开始
                        achievement.IsAchieved = (d) =>
                        {
                            return d.TaskNum >= 1;
                        };
                        break;
                    case 2://小试牛刀
                        achievement.IsAchieved = (d) =>
                        {
                            return d.TaskNum >= 5;
                        };
                        break;
                    case 3://勤劳的蜜蜂
                        achievement.IsAchieved = (d) =>
                        {
                            return d.TaskNum >= 15;
                        };
                        break;
                    case 4://知识学爆
                        achievement.IsAchieved = (d) =>
                        {
                            return d.TaskNum >= 30;
                        };
                        break;
                    case 5://初学者
                        achievement.IsAchieved = (d) =>
                        {
                            return d.FocusTimeSum >= 60;
                        };
                        break;
                    case 6://意志坚定
                        achievement.IsAchieved = (d) =>
                        {
                            return d.FocusTimeSum >= 2 * 60;
                        };
                        break;
                    case 7://专注达人
                        achievement.IsAchieved = (d) =>
                        {
                            return d.FocusTimeSum >= 5 * 60;
                        };
                        break;
                    case 8://时间管理大师
                        achievement.IsAchieved = (d) =>
                        {
                            return d.FocusTimeSum >= 8 * 60;
                        };
                        break;
                    case 9://周任务新手
                        achievement.IsAchieved = (d) =>
                        {
                            return d.TaskNumWeekly >= 10;
                        };
                        break;
                    case 10://周任务高手
                        achievement.IsAchieved = (d) =>
                        {
                            return d.TaskNumWeekly >= 20;
                        };
                        break;
                    case 11://周任务大师
                        achievement.IsAchieved = (d) =>
                        {
                            return d.TaskNumWeekly >= 50;
                        };
                        break;
                    case 12://周专注新手
                        achievement.IsAchieved = (d) =>
                        {
                            return d.FocusTimeWeekly >= 10 * 60;
                        };
                        break;
                    
                }
            }
            // 订阅事件
            OnDataUpdated += CheckAchievements;
        }
        private List<Achievement> LoadAchievementsFromDatabase()
        {           
            using(var connection = OpenConnection())
            return connection.Query<Achievement>("SELECT * FROM Achievement").ToList();
        }
        //检测函数
        private void CheckAchievements(Data data)
        {
            foreach (var achievement in achievements)
            {
                if (achievement.AchievementId >= 13)//测试限制
                    break;
                if (achievement.IsAchieved(data))
                {
                    var association = GetAchievementAssociation(data.UserId, achievement.AchievementId);
                    if (association == null || !association.IsAchieved)
                    {
                        // 成就解锁
                        AddDataAchievementAssociation(data.UserId, achievement.AchievementId);
                        AddAchievementToUser(data.UserId, achievement.AchievementId);

                        // 通知用户
                        NotifyUser(achievement);
                        UnlockAchievementDialog dialog = new UnlockAchievementDialog(GetAchievement(achievement.AchievementId));
                        dialog.OKClicked += (s, e) =>
                        {
                            // 可选：处理 OK 按钮点击事件
                            Console.WriteLine("OK button clicked in UnlockAchievementDialog.");
                        };
                        dialog.ShowDialog(); // 使用 ShowDialog 来显示模态对话框
                        AchievementUnlocked?.Invoke();
                    }
                }
            }
        }
        //通知函数
        private void NotifyUser(Achievement achievement)
        {
            //MessageBox.Show($"恭喜你解锁了成就：{achievement.Name}\n{achievement.Description}", "成就解锁");
        }


        // 打开连接
        private SQLiteConnection OpenConnection()
        {
            var connection = new SQLiteConnection(connectionString);
            connection.Open();
            return connection;
        }

        // Data management
        public void AddData(Data data)
        {
            using (var connection = OpenConnection())
            {
                connection.Execute(
                    "INSERT INTO Data (UserId, TomatoNum, TomatoNumSum, TomatoNumWeekly, FocusTime, FocusTimeWeekly, FocusTimeSum, TaskNum, TaskNumWeekly, TaskNumSum, InterruptNum, InterruptNumWeekly, InterruptNumSum,StartTime) " +
                    "VALUES (@UserId, @TomatoNum, @TomatoNumSum, @TomatoNumWeekly, @FocusTime, @FocusTimeWeekly, @FocusTimeSum, @TaskNum, @TaskNumWeekly, @TaskNumSum, @InterruptNum, @InterruptNumWeekly, @InterruptNumSum,@StartTime)",
                    data);
            }
        }
        public Data GetData(int userId)
        {
            using (var connection = OpenConnection())
            {
                //DateTime today = DateTime.Now.Date;
                string query = @"SELECT * FROM Data 
                         WHERE UserId = @UserId 
                         ORDER BY StartTime DESC
                         LIMIT 1";
                var parameters = new { UserId = userId};//, Today = today.ToString("yyyy-MM-dd") 

                var result = connection.QuerySingleOrDefault<Data>(query, parameters);

                // 打印查询结果
                if (result == null)
                {
                    MessageBox.Show("Query returned no results.");
                }
                else
                {
                    Console.WriteLine($"Query returned data");
                }

                return result;
            }
        }

        public String ChangedayofweekToString(int dayofweek)
        {
            return dayOfWeekInChinese[dayofweek];
        }

        public Dictionary<string, double> GetFocusWithDayOfWeek(int userId)
        {
            using (var connection = OpenConnection())
            {
                DateTime today = DateTime.Now.Date.AddDays(1); // 明天的日期，以包含今天的所有记录
                DateTime startDate = today.AddDays(-(int)today.DayOfWeek + 1); // 获取本周一的日期

                var data = connection.Query<Data>(
                    @"SELECT UserId, FocusTime, StartTime
              FROM Data
              WHERE UserId = @UserId AND DATE(StartTime) BETWEEN DATE(@StartDate) AND DATE(@Today)
              ORDER BY StartTime DESC",
                    new { UserId = userId, StartDate = startDate, Today = today });

                Dictionary<string, double> result = new Dictionary<string, double>();

                // 初始化结果字典，包含从周一到周日的所有天
                string[] daysOfWeek = { "星期一", "星期二", "星期三", "星期四", "星期五", "星期六", "星期日" };
                foreach (var day in daysOfWeek)
                {
                    result[day] = 0; // 初始化所有天的FocusTime为0
                }

                // 按天分组并取每组最新的一条记录
                var groupedData = new Dictionary<DateTime, Data>();
                foreach (var item in data)
                {
                    DateTime date = item.StartTime.Date;
                    if (!groupedData.ContainsKey(date))
                    {
                        groupedData[date] = item;
                    }
                }

                // 填充结果字典
                foreach (var kvp in groupedData)
                {
                    string dayOfWeekString = daysOfWeek[(int)kvp.Key.DayOfWeek == 0 ? 6 : (int)kvp.Key.DayOfWeek - 1];
                    result[dayOfWeekString] = kvp.Value.FocusTime;
                }

                return result;
            }
        }


        public void UpdateData(Data updatedData)
        {
            using (var connection = OpenConnection())
            {
                updatedData.StartTime = NowTime;
               connection.Execute("UPDATE Data SET TomatoNum = @TomatoNum, TomatoNumSum = @TomatoNumSum, TomatoNumWeekly = @TomatoNumWeekly, " +
                    "FocusTime = @FocusTime, FocusTimeWeekly = @FocusTimeWeekly, FocusTimeSum = @FocusTimeSum, TaskNum = @TaskNum, " +
                    "TaskNumWeekly = @TaskNumWeekly, TaskNumSum = @TaskNumSum, InterruptNum = @InterruptNum, InterruptNumWeekly = @InterruptNumWeekly, " +
                    "InterruptNumSum = @InterruptNumSum, StartTime=@StartTime  WHERE UserId = @UserId",
                    updatedData);
            }
            // 触发事件
            OnDataUpdated?.Invoke(updatedData);
        }

        public void RemoveData(int userId)
        {
            using (var connection = OpenConnection())
            {
                connection.Execute("DELETE FROM Data WHERE UserId = @UserId", new { UserId = userId });
            }
        }

        // Achievement management
        public void AddAchievement(Achievement achievement)
        {
            using (var connection = OpenConnection())
            {
                connection.Execute("INSERT INTO Achievement (Name, Description, ImagePath) VALUES (@Name, @Description, @ImagePath)", achievement);
            }
        }

        public Achievement GetAchievement(int achievementId)
        {
            using (var connection = OpenConnection())
            {
                return connection.QuerySingleOrDefault<Achievement>("SELECT * FROM Achievement WHERE AchievementId = @AchievementId", new { AchievementId = achievementId });
            }
        }

        public void UpdateAchievement(Achievement updatedAchievement)
        {
            using (var connection = OpenConnection())
            {
                connection.Execute("UPDATE Achievement SET Name = @Name, Description = @Description, ImagePath = @ImagePath WHERE AchievementId = @AchievementId", updatedAchievement);
            }
        }

        public void RemoveAchievement(int achievementId)
        {
            using (var connection = OpenConnection())
            {
                connection.Execute("DELETE FROM Achievement WHERE AchievementId = @AchievementId", new { AchievementId = achievementId });
            }
        }
        //返回缺少的成就的列表
        public List<Achievement> GetMissingAchievements(int userId)
        {
            // 获取用户已经获得的成就列表
            var userAchievements = GetAchievementsForUser(userId);

            // 提取用户已经获得的成就ID列表
            var userAchievementIds = userAchievements.Select(a => a.AchievementId).ToHashSet();

            // 找出所有成就中用户尚未获得的成就
            var missingAchievements = achievements
                .Where(achievement => !userAchievementIds.Contains(achievement.AchievementId))
                .ToList();

            return missingAchievements;
        }
        //返回全部成就列表
        public List<Achievement> GetAllAchievements() { return achievements; }

        // User management
        public void AddUser(User user)
        {
            using (var connection = OpenConnection())
            {
                connection.Execute("INSERT INTO User (UserName, Password, Age, Sex, Occupation, Signature) VALUES (@UserName, @Password, @Age, @Sex, @Occupation, @Signature)", user);
            }
        }

        public User GetUser(int userId)
        {
            using (var connection = OpenConnection())
            {
                return connection.QuerySingleOrDefault<User>("SELECT * FROM User WHERE UserId = @UserId", new { UserId = userId });
            }
        }

        public void UpdateUser(User updatedUser)
        {
            using (var connection = OpenConnection())
            {
                connection.Execute("UPDATE User SET UserName = @UserName, Password = @Password, Age = @Age, Sex = @Sex, Occupation = @Occupation, Signature = @Signature WHERE UserId = @UserId", updatedUser);
            }
        }

        public void RemoveUser(int userId)
        {
            using (var connection = OpenConnection())
            {
                connection.Execute("DELETE FROM User WHERE UserId = @UserId", new { UserId = userId });
            }
        }

        public bool LoginUser(string username, string password)
        {
            using (var connection = OpenConnection())
            {
                string sql = "SELECT COUNT(*) FROM User WHERE UserName = @Username AND Password = @Password";
                int count = connection.QueryFirstOrDefault<int>(sql, new { Username = username, Password = password });
                return count > 0; 
            }
        }

        public bool EnrollUser(string username)
        {
            using (var connection = OpenConnection())
            {
                string sql = "SELECT COUNT(*) FROM User WHERE UserName = @Username";
                int count = connection.QueryFirstOrDefault<int>(sql, new { Username = username });
                return count > 0;
            }
        }
        public int GetUserIdFromUserNameAnduserPassword(string userName, string password)
        {
            using (var connection = OpenConnection())
            {
                string sql = "SELECT * FROM User WHERE UserName = @UserName AND Password=@Password";
                User tmp = connection.QuerySingleOrDefault<User>("SELECT * FROM User WHERE UserName = @UserName AND Password=@Password",
                    new { UserName = userName, Password = password });
                return tmp.UserId;
            }
        }
        public User GetUserFromUserNameAnduserPassword(string userName, string password)
        {
            using (var connection = OpenConnection())
            {
                return connection.QuerySingleOrDefault<User>(sql: "SELECT * FROM [User] WHERE UserName = @UserName AND Password = @Password",
                    new { UserName = userName, Password = password });

            }
        }
        // PomodoroTask management
        public void AddPomodoroTask(PomodoroTask task)
        {
            using (var connection = OpenConnection())
            {
                connection.Execute(
                    "INSERT INTO PomodoroTask (Name, UserId, Duration, Type, IsCompleted, IsPaused, Note, StartTime, EndTime, TimeLeft) " +
                    "VALUES (@Name, @UserId, @Duration, @Type, @IsCompleted, @IsPaused, @Note, @StartTime, @EndTime, @TimeLeft)",
                    task);
            }
            UpdateDataByTask(task);
        }

        public void RemovePomodoroTask(int taskId)
        {
            using (var connection = OpenConnection())
            {
                connection.Execute("DELETE FROM PomodoroTask WHERE TaskId = @TaskId", new { TaskId = taskId });
            }
        }

        public PomodoroTask GetPomodoroTask(int taskId)
        {
            using (var connection = OpenConnection())
            {
                return connection.QuerySingleOrDefault<PomodoroTask>("SELECT * FROM PomodoroTask WHERE TaskId = @TaskId", new { TaskId = taskId });
            }
        }
        public void UpdateDataByTask(PomodoroTask Task) {
            // 更新Data表
            var data = GetDataByUserIdAndDate(Task.UserId, DateTime.Now);

            if (data == null)
            {
                data = new Data
                {
                    UserId = Task.UserId,
                    StartTime = DateTime.Now.Date,
                    TomatoNum = 0,
                    TomatoNumSum = 0,
                    TomatoNumWeekly = 0,
                    FocusTime = 0,
                    FocusTimeWeekly = 0,
                    FocusTimeSum = 0,
                    TaskNum = 0,
                    TaskNumWeekly = 0,
                    TaskNumSum = 0
                };
            }

            // 更新每日和每周的任务完成数和专注时间
            if (Task.IsCompleted)
            {
                data.TomatoNum++;
                data.TomatoNumSum++;
                data.TaskNum++;
                data.TaskNumSum++;
                data.FocusTime += Task.Duration;
                data.FocusTimeSum += Task.Duration;
            }
            //更新周数据
            UpdateWeeklyData(data);
            // 更新数据
            UpdateData(data);
        }
        public void UpdatePomodoroTask(PomodoroTask updatedTask)
        {
            using (var connection = OpenConnection())
            {
                connection.Execute(
                    "UPDATE PomodoroTask SET Name = @Name, UserId = @UserId, Duration = @Duration, Type = @Type, IsCompleted = @IsCompleted, IsPaused = @IsPaused, Note = @Note, StartTime = @StartTime, EndTime = @EndTime, TimeLeft = @TimeLeft " +
                    "WHERE TaskId = @TaskId",
                    updatedTask);
            }
            
        }
        public Data GetDataByUserIdAndDate(int userId, DateTime date)
        {
            using (var connection = OpenConnection())
            {
                var result = connection.Query<Data>(
                    "SELECT * FROM Data WHERE UserId = @UserId AND DATE(StartTime) = DATE(@Date) ORDER BY StartTime DESC",
                    new { UserId = userId, Date = date });

                return result.FirstOrDefault();
            }
        }
        public void UpdateWeeklyData(Data data)
        {
            DateTime startOfWeek = DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek + (int)DayOfWeek.Monday);
            DateTime endOfWeek = startOfWeek.AddDays(7);

            using (var connection = OpenConnection())
            {
                var weeklyData = connection.Query<Data>(
                    "SELECT * FROM Data WHERE UserId = @UserId AND StartTime BETWEEN @StartOfWeek AND @EndOfWeek",
                    new { UserId = data.UserId, StartOfWeek = startOfWeek, EndOfWeek = endOfWeek });

                data.TomatoNumWeekly = 0;
                data.FocusTimeWeekly = 0;
                data.TaskNumWeekly = 0;

                foreach (var item in weeklyData)
                {
                    data.TomatoNumWeekly += item.TomatoNum;
                    data.FocusTimeWeekly += item.FocusTime;
                    data.TaskNumWeekly += item.TaskNum;
                }
            }
        }

        public List<PomodoroTask> GetPomodoroTasksForUser(int userId)
        {
            using (var connection = OpenConnection())
            {
                return connection.Query<PomodoroTask>("SELECT * FROM PomodoroTask WHERE UserId = @UserId", new { UserId = userId }).ToList();
            }
        }

        // Data and Achievement association management
        //添加联系：表明用户解锁了成就
        public void AddDataAchievementAssociation(int userId, int achievementId)
        {
            using (var connection = OpenConnection())
            {
                string AchievedDate = DateTime.Now.ToString();
                connection.Execute("INSERT INTO UserAchievement (UserId, AchievementId, IsAchieved, AchievedDate) VALUES (@UserId, @AchievementId, 1, @AchievedDate)",
                    new { UserId = userId, AchievementId = achievementId, AchievedDate = DateTime.Now });
            }
        }

        public void AddAchievementToUser(int userId, int achievementId)
        {
            var data = GetData(userId);
            var achievement = GetAchievement(achievementId);

            if (data != null && achievement != null)
            {
                AddDataAchievementAssociation(userId, achievementId);
            }
        }

        public List<Achievement> GetAchievementsForUser(int userId)
        {
            using (var connection = OpenConnection())
            {
                // 获取与用户相关的成就ID列表
                var achievementIds = connection.Query<int>(
                    "SELECT AchievementId FROM UserAchievement WHERE UserId = @UserId",
                    new { UserId = userId }).ToList();

                if (achievementIds.Count > 0)
                {
                    // 获取成就列表
                    return connection.Query<Achievement>(
                        "SELECT * FROM Achievement WHERE AchievementId IN @AchievementIds",
                        new { AchievementIds = achievementIds }).ToList();
                }
            }
            return new List<Achievement>();
        }
        
        public List<User> GetUserFocusTimeSumRankList()
        {
            var users = new List<User>();

            try
            {
                Debug.WriteLine("Attempting to open database connection...");
                using (var connection = OpenConnection())
                {
                    Debug.WriteLine("Database connection opened successfully.");

                    var userDataDict = new Dictionary<int, Data>();

                    // 查询用户数据
                    var userDataQuery = @"
                SELECT UserId, FocusTimeWeekly, StartTime
                FROM Data
                WHERE StartTime >= @weekStart";

                    connection.Query<Data>(userDataQuery, new { weekStart = GetStartOfWeek() })
                        .ToList()
                        .ForEach(data => userDataDict[data.UserId] = data);

                    // 查询用户信息
                    var userQuery = @"
                SELECT UserId, UserName, Password, Age, Sex, Occupation, Signature
                FROM User";

                    users = connection.Query<User>(userQuery)
                        .ToList();

                    // 合并用户信息和数据
                    foreach (var user in users)
                    {
                        if (userDataDict.TryGetValue(user.UserId, out var userData))
                        {
                            user.data = userData;
                        }
                        else
                        {
                            user.data = new Data();
                        }
                    }

                    // 按周专注时间倒序排列用户列表
                    users = users.OrderByDescending(u => u.data.FocusTimeWeekly).ToList();

                    Debug.WriteLine("Users loaded and ranked successfully.");
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine($"Error in GetUserFocusTimeSumRankList: {ex.Message}");
                throw; // 抛出异常以便调用方能够进一步处理
            }

            return users;
        }

        private DateTime GetStartOfWeek()
        {
            DateTime today = DateTime.Today;
            int diff = today.DayOfWeek - DayOfWeek.Monday;
            if (diff < 0)
            {
                diff += 7;
            }
            return today.AddDays(-diff).Date;
        }


        public Data_Achievement_Association GetAchievementAssociation(int userId, int achievementId)
        {
            using (var connection = OpenConnection())
            {
                return connection.QuerySingleOrDefault<Data_Achievement_Association>(
                    "SELECT * FROM UserAchievement WHERE UserId = @UserId AND AchievementId = @AchievementId",
                    new { UserId = userId, AchievementId = achievementId });
            }
        }

        public List<Data_Achievement_Association> GetAchievementAssociationsForUser(int userId)
        {
            using (var connection = OpenConnection())
            {
                return connection.Query<Data_Achievement_Association>("SELECT * FROM UserAchievement WHERE UserId = @UserId", new { UserId = userId }).ToList();
            }
        }

        
        //解锁条件

    }
}
